package com.sendi.common.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
  
public class JExcelUtils {   
	  private WritableWorkbook workbook;
	  private WritableSheet sheet;
	  private WritableCellFormat format;
	  private int column;
	  private int row;
	  private String path;
	  
	  public JExcelUtils(){
		  setDataFormat();
		  setRow(0);
	  }
	  
    /**  
     * 生成Excel文件  
     * @param response         直接导出   
     */  
   public void createExcel(HttpServletResponse response,String sheetName,String[] title){ 
	   
	   setColumn(title.length);
	   setDataFormat();
	   response.setContentType("application/x-msdownload");
	   response.setContentType("application/msexcel");
	   
       try{   
    	   response.setHeader("Content-Disposition", "attachment;" + " filename=" + new String(sheetName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
    	   OutputStream os=response.getOutputStream();
		   os=response.getOutputStream();
    	   this.workbook=Workbook.createWorkbook(os);           
           this.sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表   

           sheet.getSettings().setDefaultColumnWidth(20);//设置列的默认宽度
           
           this.sheet.getSettings().setAutomaticFormulaCalculation(true);
           
           mergeCellsAndInsertData(0,getRow(),getColumn()-1,getRow(),sheetName); //合并单元表格并插入标题
           setRow(row+1);
           setTitleFormat();
           insertcolumnData(title);  //插入横标  
           setDataFormat();
       }catch(Exception e){   
           e.printStackTrace();   
       }  
   }   
   
   /**  
    * 生成Excel文件  
    * @param response         直接导出   
    */  
  public void createExcel(HttpServletResponse response,String sheetName){
	   setDataFormat();
	   response.setContentType("application/x-msdownload");
	   response.setContentType("application/msexcel");
	   
      try{   
   	   response.setHeader("Content-Disposition", "attachment;" + " filename=" + new String(sheetName.getBytes("gb2312"), "ISO-8859-1") + ".xls");
   	   OutputStream os=response.getOutputStream();
		   os=response.getOutputStream();
   	   this.workbook=Workbook.createWorkbook(os);           
          this.sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表   
          sheet.getSettings().setDefaultColumnWidth(12);//设置列的默认宽度
          this.sheet.getSettings().setAutomaticFormulaCalculation(true);
      }catch(Exception e){   
          e.printStackTrace();   
      }  
  }
   
   /**  
    * 生成Excel文件  
    * @param path         文件路径    
    */  
   public void createExcel(String path,String sheetName,String[] title){ 	
	  setColumn(title.length);
      try{       
    	  OutputStream os=new FileOutputStream(path);
    	  this.workbook=Workbook.createWorkbook(os);           
          this.sheet = workbook.createSheet(sheetName, 0); //添加第一个工作表   
          
          sheet.getSettings().setDefaultColumnWidth(20);//设置列的默认宽度
          
          this.sheet.getSettings().setAutomaticFormulaCalculation(true);
          
          mergeCellsAndInsertData(0,0,getColumn()-1,0,sheetName); //合并单元表格并插入标题
          setRow(row+1);
          setTitleFormat();
          insertcolumnData(title);  //插入横标     
          setDataFormat();
      }catch(Exception e){   
          e.printStackTrace();   
      }  
  }   
   
   public void setwidth(int[] width){
	   for(int i=0;i<width.length;i++){
		   this.sheet.setColumnView(i, width[i]); //设置列的宽度
	   }
   }
  
   /**  
    * 插入单个数据 
    * @param sheet  
    * @param col  
    * @param column  
    * @param formula 
    */  
   public void insertFormula(Integer col,Integer column,String data){   
       try{   
    	   Label f = new Label(col,column,data,this.format);//new Formula(col,column,format);   
    	   this.sheet.addCell(f);   
       }catch(Exception e){   
           e.printStackTrace();   
       }   
   }   
      
   /**  
    * 插入一行数据  
    * @param sheet       工作表  
    * @param content     内容  
    */  
   public void insertcolumnData(String[] dataArr){   
       try{   
           Label label;   
           for(int i=0;i<dataArr.length;i++){   
               label = new Label(i,getRow(),dataArr[i],this.format);   
               this.sheet.addCell(label);                  
           }
           setRow(getRow()+1);
       }catch(Exception e){   
           e.printStackTrace();   
       }   
   }
   
   /**  
    * 插入一行数据  
    * @param sheet       工作表  
    * @param column      列号  
    * @param content     内容  
    */  
   public void insertRowData(int startColumn,String[] dataArr){   
       try{   
           Label label;   
           for(int i=0;i<dataArr.length;i++){   
               label = new Label(i+startColumn,getRow(),dataArr[i],this.format);   
               this.sheet.addCell(label);                  
           }
           setRow(getRow()+1);
       }catch(Exception e){   
           e.printStackTrace();   
       }   
   }
   
   /**  
    * 关闭Excel文件    
    */  
  public void closeExcelFile(){      
      try{          	  
          this.workbook.write();    
          this.workbook.close();   
      }catch(Exception e){   
          e.printStackTrace();   
      }   
  }       
      
   /**  
    * 合并单元格，并插入数据  
    * @param sheet  
    * @param col_start  
    * @param column_start  
    * @param col_end  
    * @param column_end  
    * @param data  
    * @param format  
    */  
   public void mergeCellsAndInsertData(Integer col_start,Integer column_start,Integer col_end,Integer column_end,Object data){   
      try{   
    	  this.sheet.mergeCells(col_start,column_start,col_end,column_end);//左上角到右下角
          Label label = new Label(col_start,column_start,data.toString(),this.format);  
          System.out.println(this.sheet);
          System.out.println(label);
          this.sheet.addCell(label);                    
      }catch(Exception e){   
          e.printStackTrace();   
      }   
  
   }  
   
   /**  
    * 得到数据表头格式  
    * @return  
    */  
   public void setTitleFormat(){ 
       try {  
    	   WritableCellFormat wcf;
    	   
           //字体样式 
           WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.NO_BOLD,false);//最后一个为是否italic   
           wf.setColour(Colour.BLACK);   
           wcf = new WritableCellFormat(wf);   
           //对齐方式   
           wcf.setAlignment(Alignment.CENTRE);  
           wcf.setVerticalAlignment(VerticalAlignment.CENTRE);   
           //边框   
           //wcf.setBorder(Border.ALL,BorderLineStyle.THIN); 
           //背景色   
           wcf.setBackground(Colour.WHITE);
           // 设置边框
           wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
           // 设置自动换行
           wcf.setWrap(true);
           this.format = wcf;
           
       } catch (WriteException e) {   
        e.printStackTrace();   
       }     
   }   
      
   /**  
    * 得到数据格式  
    * @return  
    */  
   public void setDataFormat(){  
       WritableCellFormat wcf;
       //字体样式   
       WritableFont wf = new WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD,false);//最后一个为是否italic     
       wcf = new WritableCellFormat(wf);
       try {
		wcf.setAlignment(Alignment.LEFT);
		wcf.setVerticalAlignment(VerticalAlignment.TOP);
		// 设置边框
        wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
        // 设置自动换行
        wcf.setWrap(true);
	} catch (WriteException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
       this.format = wcf;
   }   
      
   /**  
    * 给单元格加注释  
    * @param label  
    * @param comments  
    */  
   public void setCellComments(Object label,String comments){   
       WritableCellFeatures cellFeatures = new WritableCellFeatures();   
       cellFeatures.setComment(comments);   
       if(label instanceof jxl.write.Number){   
           jxl.write.Number num = (jxl.write.Number)label;   
           num.setCellFeatures(cellFeatures);   
       }else if(label instanceof jxl.write.Boolean){   
           jxl.write.Boolean bool = (jxl.write.Boolean)label;   
           bool.setCellFeatures(cellFeatures);   
       }else if(label instanceof jxl.write.DateTime){   
           jxl.write.DateTime dt = (jxl.write.DateTime)label;   
           dt.setCellFeatures(cellFeatures);   
       }else{   
           Label _label = (Label)label;   
           _label.setCellFeatures(cellFeatures);   
       }   
   }   
      
   /**  
   * 读取excel  
   * @param inputFile  
   * @param inputFileSheetIndex  
   * @thcolumns Exception  
   */  
   public ArrayList<List> readDataFromExcel(File inputFile, int inputFileSheetIndex){   
      ArrayList<List> celllist = new ArrayList<List>(); 
      ArrayList<String> columnlist = null;
      Workbook book = null;   
      Cell cell = null;   
      WorkbookSettings setting = new WorkbookSettings();    
      java.util.Locale locale = new java.util.Locale("zh","CN");    
      setting.setLocale(locale);   
      setting.setEncoding("ISO-8859-1");   
      try{   
          book = Workbook.getWorkbook(inputFile, setting);   
      }catch(Exception e){   
          e.printStackTrace();     
      }   
  
      Sheet sheet = book.getSheet(inputFileSheetIndex);   
      for (int columnIndex = 0; columnIndex < sheet.getColumns(); columnIndex++) {//行   
    	  columnlist = new ArrayList<String>();
          for (int colIndex = 0; colIndex < sheet.getColumns(); colIndex++) {//列   
              cell = sheet.getCell(colIndex, columnIndex);   
              columnlist.add(cell.getContents());   
          } 
          celllist.add(columnlist);
      }   
      book.close();   
  
      return celllist;   
   } 
   
   public void insertmapData(List<Map> data,String[] column,int row){
	   if(column.length!=getColumn()){
		   System.out.print("数据如入有误");
	   }else{
		   int j = row-1;
		   for (Map map:data){
			   for (int i=0;i<row;i++){
			      if(map.get(column[i])==null)
			      {
			    	  System.out.print("找不到对象："+column[i]);
			      }
			      else
			      {
			    	  insertFormula(i,j,map.get(column[i]).toString());
			      }
			   }
			   j++;
		   }
	   }
	   
   }
   
   public void insertData(String[][] dataArr){
	   for(int i=0;i<dataArr.length;i++){
	 		insertcolumnData(dataArr[i]);
	 	}	
	   
   }
      
   /**  
    * 打开文件看看  
    * @param exePath  
    * @param filePath  
    */  
   public void openExcel(String exePath,String filePath){   
       Runtime r=Runtime.getRuntime();    
       String cmd[]={exePath,filePath};    
       try{    
           r.exec(cmd);    
       }catch(Exception e){   
           e.printStackTrace();   
       }   
   }   
      
   public static void main(String[] args){   
       String[] titles = {"学号dddddddddddddddddddddddddddddddddddd","姓名","语文","数学","英语","总分"}; 
       String[] data = {"2000","姓名","语文","数学","英语","总分"}; 
       int[] width = {10,20,30,20,20,30}; 
       JExcelUtils jxl = new JExcelUtils();        
       String filePath = "E:/test.xls";
       jxl.createExcel(filePath, "成绩单", titles);
       jxl.setwidth(width);
       jxl.insertcolumnData(data);
       jxl.closeExcelFile();
       System.out.println("success!");
   }  

public WritableWorkbook getWorkbook() {
	return workbook;
}

public void setWorkbook(WritableWorkbook workbook) {
	this.workbook = workbook;
}

public WritableSheet getSheet() {
	return sheet;
}

public void setSheet(WritableSheet sheet) {
	this.sheet = sheet;
}

public WritableCellFormat getFormat() {
	return format;
}

public void setFormat(WritableCellFormat format) {
	this.format = format;
}


public int getColumn() {
	return column;
}

public void setColumn(int column) {
	this.column = column;
}

public int getRow() {
	return row;
}

public void setRow(int row) {
	this.row = row;
}

public String getPath() {
	return path;
}

public void setPath(String path) {
	this.path = path;
} 
   
   
}  